﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Configuration.Provider;
using System.Collections.Specialized;
using System.Web.Security;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Diagnostics;
using System.Security.Cryptography;

namespace Components.SQL
{
    public sealed class ErendoraMembershipProvider : MembershipProvider
    {

        private int newPasswordLength = 8;
        private string eventSource = "ErendoraMembershipProvider";
        private string eventLog = "Application";
        private string exceptionMessage = "An exception occurred. Please check the Event Log.";
        private string connectionString;

        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
                throw new ArgumentNullException("config");

            if (name == null || name.Length == 0)
                name = "ErendoraMembershipProvider";

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Erendora ODBC Membership provider");
            }

            base.Initialize(name, config);

            pApplicationName = GetConfigValue(config["applicationName"],
                                                System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
            pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
            pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
            pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
            pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
            pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
            pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
            pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
            pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
            pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"));
            pHash = Convert.ToString(GetConfigValue(config["hash"],"kikugalanet"));

            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if( ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "" )
            {
                throw new ProviderException("Connection string cannot be blank");
            }

            connectionString = ConnectionStringSettings.ConnectionString;
        }

        private string GetConfigValue(string configValue, string defaultValue)
        {
            if (String.IsNullOrEmpty(configValue))
                return defaultValue;

            return configValue;
        }

        private string pApplicationName;
        private bool pEnablePasswordReset;
        private bool pEnablePasswordRetrieval;
        private bool pRequiresQuestionAndAnswer;
        private bool pRequiresUniqueEmail;
        private int pMaxInvalidPasswordAttempts;
        private int pPasswordAttemptWindow;
        private string pHash;
        private int pMinRequiredNonAlphanumericCharacters;
        private int pMinRequiredPasswordLength;
        private string pPasswordStrengthRegularExpression;
        private bool pWriteExceptionsToEventLog;

        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }

        public string Hash
        {
            get { return pHash; }
            set { pHash = value; }
        }

        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        public override int MaxInvalidPasswordAttempts
        {
            get { return pMaxInvalidPasswordAttempts; }
        }

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return pMinRequiredNonAlphanumericCharacters; }
        }

        public override int MinRequiredPasswordLength
        {
            get { return pMinRequiredPasswordLength; }
        }

        public override int PasswordAttemptWindow
        {
            get { return pPasswordAttemptWindow; }
        }

        public override System.Web.Security.MembershipPasswordFormat PasswordFormat
        {
            get { return MembershipPasswordFormat.Hashed; }
        }

        public override string PasswordStrengthRegularExpression
        {
            get { return pPasswordStrengthRegularExpression; }
        }

        public override bool RequiresQuestionAndAnswer
        {
            get { return pRequiresQuestionAndAnswer; }
        }

        public override bool RequiresUniqueEmail
        {
            get { return pRequiresUniqueEmail; }
        }

        public override bool EnablePasswordReset
        {
            get { return pEnablePasswordReset; }
        }

        public override bool EnablePasswordRetrieval
        {
            get { return pEnablePasswordRetrieval; }
        }

        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            if (!ValidateUser(username, oldPassword))
                return false;

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
                if (args.FailureInformation != null)
                    throw args.FailureInformation;
                else
                    throw new MembershipPasswordException("Change password canceled due to new password validation failure.");

            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{? = CALL usp_ChangePW (?,?)}", conn);

            OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
            prm.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
            cmd.Parameters.Add("@pw", OdbcType.VarChar, 32).Value = Md5Password(newPassword);

            int rowsAffected = 0;

            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
                rowsAffected = (prm.Value == null ? 0 : Convert.ToInt32(prm.Value));
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePassword");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return (rowsAffected > 0);
        }

        public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
        {
            if (!ValidateUser(username, password))
                return false;

            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{? = CALL webChangeQuestionAndAnswer (?,?,?,?)}", conn);

            OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
            prm.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("@question", OdbcType.VarChar, 255).Value = newPasswordQuestion;
            cmd.Parameters.Add("@answer", OdbcType.VarChar, 255).Value = Md5Password(newPasswordAnswer);
            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            int rowsAffected = 0;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                rowsAffected = (prm.Value == null ? 0 : Convert.ToInt32(prm.Value));
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
            return (rowsAffected > 0);
        }

        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
        {
            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
            {
                status = MembershipCreateStatus.DuplicateEmail;
                return null;
            }

            MembershipUser u = GetUser(username, false);

            if (u == null)
            {
                DateTime createDate = DateTime.Now;
                if (providerUserKey == null)
                {
                    providerUserKey = Guid.NewGuid();
                }
                else
                {
                    if (!(providerUserKey is Guid))
                    {
                        status = MembershipCreateStatus.InvalidProviderUserKey;
                        return null;
                    }
                }

                OdbcConnection conn = new OdbcConnection(connectionString);
                OdbcCommand cmd = new OdbcCommand("{? = CALL webCreateAcc (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}",conn);

                OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
                prm.Direction = ParameterDirection.ReturnValue;

                cmd.Parameters.Add("@pkid", OdbcType.UniqueIdentifier).Value = providerUserKey;
                cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
                cmd.Parameters.Add("@password", OdbcType.VarChar, 32).Value = Md5Password(password);
                cmd.Parameters.Add("@email", OdbcType.VarChar, 120).Value = email;
                cmd.Parameters.Add("@passwordQuestion", OdbcType.VarChar, 255).Value = (String.IsNullOrEmpty(passwordQuestion) ? "" : passwordQuestion);
                cmd.Parameters.Add("@passwordAnswer", OdbcType.VarChar, 255).Value = (String.IsNullOrEmpty(passwordAnswer) ? "" : Md5Password(passwordAnswer));
                cmd.Parameters.Add("@isApproved", OdbcType.Bit).Value = isApproved;
                cmd.Parameters.Add("@creationDate", OdbcType.DateTime).Value = createDate.ToString("yyyy-MM-dd hh:mm:ss");
                cmd.Parameters.Add("@lastPasswordChangeDate", OdbcType.DateTime).Value = createDate.ToString("yyyy-MM-dd hh:mm:ss");
                cmd.Parameters.Add("@lastActivityDate", OdbcType.DateTime).Value = createDate.ToString("yyyy-MM-dd hh:mm:ss");
                cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;
                cmd.Parameters.Add("@isLockedOut", OdbcType.Bit).Value = false;
                cmd.Parameters.Add("@lastLockedOutDate", OdbcType.DateTime).Value = createDate.ToString("yyyy-MM-dd hh:mm:ss");
                cmd.Parameters.Add("@failedPasswordAttemptCount", OdbcType.Int).Value = 0;
                cmd.Parameters.Add("@failedPasswordAttemptWindowStart", OdbcType.DateTime).Value = createDate.ToString("yyyy-MM-dd hh:mm:ss");
                cmd.Parameters.Add("@failedPasswordAnswerAttemptCount", OdbcType.Int).Value = 0;
                cmd.Parameters.Add("@failedPasswordAnswerAttemptWindowStart", OdbcType.DateTime).Value = createDate.ToString("yyyy-MM-dd hh:mm:ss");

                try
                {
                    conn.Open();

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(prm.Value) > 0)
                    {
                        status = MembershipCreateStatus.Success;
                    }
                    else
                    {
                        status = MembershipCreateStatus.UserRejected;
                    }
                }
                catch (OdbcException e)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "CreateUser");
                        throw new ProviderException(exceptionMessage);
                    }
                    else
                    {
                        status = MembershipCreateStatus.ProviderError;
                        throw e;
                    }
                    
                }
                finally
                {
                    conn.Close();
                }

                return GetUser(username, false);
            }
            else
            {
                status = MembershipCreateStatus.DuplicateUserName;
            }
            return null;
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{? = CALL webDeleteUser (?,?,?)}", conn);
            OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
            prm.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
            cmd.Parameters.Add("@allRelData", OdbcType.Bit).Value = deleteAllRelatedData;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "DeleteUser");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
            return (prm.Value != null && Convert.ToInt32(prm.Value) > 0);
        }

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM ACCOUNT_TBL WHERE email LIKE ?", conn);
            cmd.Parameters.Add("@emailSearch", OdbcType.VarChar, 255).Value = emailToMatch;

            MembershipUserCollection users = new MembershipUserCollection();

            OdbcDataReader reader = null;
            totalRecords = 0;

            try
            {
                conn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0)
                    return users;

                cmd.CommandText = "{CALL webGetUsersByEmail (?,?)}";
                cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex)
                        cmd.Cancel();

                    counter++;
                }
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersByEmail");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if( reader != null )
                    reader.Close();
                conn.Close();
            }
            return users;
        }

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM ACCOUNT_WEB_TBL WHERE account LIKE ? AND applicationName = ?", conn);
            cmd.Parameters.Add("@userSearch", OdbcType.VarChar, 32).Value = usernameToMatch;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            MembershipUserCollection users = new MembershipUserCollection();

            OdbcDataReader reader = null;
            totalRecords = 0;

            try
            {
                conn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0)
                    return users;

                cmd.CommandText = "{CALL webGetUsersByName (?,?)}";

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex)
                        cmd.Cancel();

                    counter++;
                }
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "FindUsersByName");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            return users;
        }

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM ACCOUNT_WEB_TBL WHERE applicationName = ?", conn);
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            MembershipUserCollection users = new MembershipUserCollection();

            OdbcDataReader reader = null;
            totalRecords = 0;

            try
            {
                conn.Open();
                totalRecords = (int)cmd.ExecuteScalar();

                if (totalRecords <= 0)
                    return users;

                cmd.CommandText = "{CALL webGetAllUsers (?)}";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

                reader = cmd.ExecuteReader();

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                while (reader.Read())
                {
                    if (counter >= startIndex)
                    {
                        MembershipUser u = GetUserFromReader(reader);
                        users.Add(u);
                    }

                    if (counter >= endIndex)
                        cmd.Cancel();

                    counter++;
                }
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetAllUsers");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            return users;
        }

        public override int GetNumberOfUsersOnline()
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM ACCOUNT_WEB_TBL WHERE LastActivityDate > ? AND ApplicationName = ?", conn);
            cmd.Parameters.Add("@CompareDate", OdbcType.DateTime).Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            int numOnline = 0;

            try
            {
                conn.Open();

                numOnline = (int)cmd.ExecuteScalar();
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNumberOfUsersOnline");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
            return numOnline;
        }

        public override string GetPassword(string username, string answer)
        {
            throw new ProviderException("Cannot retreive hashed passwords");
        }

        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webGetUser (?,?)}", conn);
            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            ErendoraMembershipUser u = null;
            OdbcDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    u = GetUserFromReader(reader);

                    if (userIsOnline)
                    {
                        OdbcCommand updateCmd = new OdbcCommand("{CALL webUpdateActivity (?,?,?)}", conn);
                        updateCmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
                        updateCmd.Parameters.Add("@lastActivityDate", OdbcType.DateTime).Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                        updateCmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

                        updateCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser(String, Boolean)");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            return u;
        }

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            throw new ProviderException("Provider user key not supported");
        }

        public override string GetUserNameByEmail(string email)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webGetUserNameByEmail (?,?)}", conn);

            cmd.Parameters.Add("@email", OdbcType.VarChar, 120).Value = email;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            string username = "";
            try
            {
                conn.Open();

                username = (string)cmd.ExecuteScalar();
                if (username == null)
                    username = "";
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUserNameByEmail");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
            return username;
        }

        public override string ResetPassword(string username, string answer)
        {
            if (!EnablePasswordReset)
            {
                throw new NotSupportedException("Password reset is not enabled.");
            }

            if (answer == null && RequiresQuestionAndAnswer)
            {
                UpdateFailureCount(username, "passwordAnswer");

                throw new ProviderException("Password answer required for password reset.");
            }

            string newPassword = System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                    throw args.FailureInformation;
                else
                    throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
            }

            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webGetPasswordAnswer (?,?)}", conn);

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            int rowsAffected = 0;
            string passwordAnswer = "";
            OdbcDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();

                    if (reader.GetBoolean(1))
                    {
                        throw new MembershipPasswordException("The supplied user is locked out.");
                    }

                    passwordAnswer = reader.GetString(0);
                }
                else
                {
                    throw new MembershipPasswordException("The supplied user name is not found.");
                }

                if (RequiresQuestionAndAnswer && !CheckPassword(answer,passwordAnswer))
                {
                    UpdateFailureCount(username, "passwordAnswer");

                    throw new MembershipPasswordException("Incorrect password answer.");
                }

                OdbcCommand updateCmd = new OdbcCommand("{? = CALL webResetPassword (?,?,?,?)}", conn);

                OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
                prm.Direction = ParameterDirection.ReturnValue;

                updateCmd.Parameters.Add("@account",OdbcType.VarChar,32).Value = username;
                updateCmd.Parameters.Add("@lastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                updateCmd.Parameters.Add("@password",OdbcType.VarChar,32).Value = Md5Password(newPassword);
                updateCmd.Parameters.Add("@applicationName",OdbcType.VarChar,255).Value = pApplicationName;

                updateCmd.ExecuteNonQuery();

                rowsAffected = (prm.Value == null ? 0 : Convert.ToInt32(prm.Value));
            }
            catch( OdbcException e )
            {
                if( WriteExceptionsToEventLog )
                {
                    WriteToEventLog(e,"ResetPassword");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if( reader != null ) 
                    reader.Close();
                conn.Close();
            }
            if( rowsAffected > 0 )
                return newPassword;
            else
                throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
        }

        public override bool UnlockUser(string userName)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{? = CALL webUnlockUser (?,?,?)}", conn);

            OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
            prm.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = userName;
            cmd.Parameters.Add("@lastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            int rowsAffected = 0;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                rowsAffected = (prm.Value == null ? 0 : Convert.ToInt32(prm.Value));
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UnlockUser");
                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return (rowsAffected > 0);
        }

        public override void UpdateUser(System.Web.Security.MembershipUser user)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webUpdateUser (?,?,?,?)}", conn);

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = user.UserName;
            cmd.Parameters.Add("@email", OdbcType.VarChar, 120).Value = user.Email;
            cmd.Parameters.Add("@isApproved", OdbcType.Bit).Value = user.IsApproved;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UpdateUser");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
        }

        public override bool ValidateUser(string username, string password)
        {
            bool isValid = false;

            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webGetPassword (?,?)}", conn);

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            OdbcDataReader reader = null;
            bool isApproved = false;
            string pwd = "";

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    pwd = reader.GetString(0);
                    isApproved = reader.GetBoolean(1);
                }
                else
                {
                    return false;
                }

                reader.Close();

                if (CheckPassword(password, pwd))
                {
                    if (isApproved)
                    {
                        isValid = true;

                        OdbcCommand updateCmd = new OdbcCommand("{CALL webSetLoginTime (?,?,?)}", conn);

                        updateCmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
                        updateCmd.Parameters.Add("@lastLoginDate", OdbcType.DateTime).Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                        updateCmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

                        updateCmd.ExecuteNonQuery();
                    }
                }
                else
                {
                    conn.Close();

                    UpdateFailureCount(username, "password");
                }
            }
            catch (OdbcException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ValidateUser");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            return isValid;
        }

        private void UpdateFailureCount(string username, string failureType)
        {

        }

        private void WriteToEventLog(Exception e, string action)
        {
            EventLog log = new EventLog();
            log.Source = eventSource;
            log.Log = eventLog;

            string message = "An exception occurred communicating with the data source.\n\n";
            message += "Action: " + action + "\n\n";
            message += "Exception: " + e.ToString();

            log.WriteEntry(message);
        }

        private bool CheckPassword(string password, string dbpassword)
        {
            return (Md5Password(password) == dbpassword);
        }

        private string Md5Password(string password)
        {
            using (MD5 md5Hash = MD5.Create())
            {
                string input = pHash + password;
                byte[] data = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input));

                StringBuilder sBuilder = new StringBuilder();

                for (int i = 0; i < data.Length; ++i)
                {
                    sBuilder.Append(data[i].ToString("x2"));
                }
                return sBuilder.ToString().ToLower();
            }
        }

        private ErendoraMembershipUser GetUserFromReader(OdbcDataReader reader)
        {
            object providerUserKey = reader.GetValue(0);
            string username = reader.GetString(1);
            string email = reader.GetString(2);

            string passwordQuestion = "";
            if (reader.GetValue(3) != DBNull.Value)
                passwordQuestion = reader.GetString(3);

            bool isApproved = reader.GetBoolean(4);
            bool isLockedOut = reader.GetBoolean(5);
            DateTime creationDate = reader.GetDateTime(6);
            DateTime lastLoginDate = new DateTime();
            if (reader.GetValue(7) != DBNull.Value)
                lastLoginDate = reader.GetDateTime(7);
            DateTime lastActivityDate = reader.GetDateTime(8);
            DateTime lastPasswordChangeDate = reader.GetDateTime(9);
            DateTime lastLockedOutDate = reader.GetDateTime(10);

            ErendoraMembershipUser u = new ErendoraMembershipUser(this.Name,
                                                    username,
                                                    providerUserKey,
                                                    email,
                                                    passwordQuestion,
                                                    "",
                                                    isApproved,
                                                    isLockedOut,
                                                    creationDate,
                                                    lastLoginDate,
                                                    lastActivityDate,
                                                    lastPasswordChangeDate,
                                                    lastLockedOutDate);
            return u;
        }
    }
}
